package org.auf.model.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.LinkedList;

import org.apache.log4j.Logger;
import org.auf.model.dao.ITestDAO;
import org.auf.to.Question;
import org.auf.to.Test;

public class TestDAOJDBC implements ITestDAO {
	static Logger log = Logger.getLogger(TestDAOJDBC.class.getName());

	@Override
	public Test selectRamdomTest() {
		String id=null;
		
		ConnectionManager cm = ConnectionManager.getInstance();
		Connection db = cm.checkOut();
		
		try{
			PreparedStatement st = db.prepareStatement("SELECT id FROM tbltest ORDER BY RAND() LIMIT 0,1");
			ResultSet rs = st.executeQuery();
			if (rs.next()){
				id= rs.getString("id");
			}

		}catch(Exception e){log.error("Error al generar la sentencia SQL y ejecutarla: "+e);}

		cm.checkIn(db);
		// TODO Auto-generated method stub
		return (id==null)? null : selectTestById(id);
	}

	@Override
	public Test selectTestById(String id) {
		Test test =new Test();
		test.setId(id);
		test.setQuestions(new LinkedList<Question>());
		
		ConnectionManager cm = ConnectionManager.getInstance();
		Connection db = cm.checkOut();
		
		try{
			PreparedStatement st = db.prepareStatement("SELECT * FROM tblquestions WHERE test=? ORDER BY id");
			st.setString(1, id);
			ResultSet rs = st.executeQuery();
			while (rs.next()){
				Question question=new Question();
				question.setId(rs.getString("id"));
				question.setQuestion(rs.getString("question"));
				for(int i=1;i<=4;i++)question.setAnswer(i,rs.getString("answer"+i));
				question.setCorrect(rs.getInt("correct"));
				test.getQuestions().add(question);
			}

		}catch(Exception e){test=null;log.error("Error al generar la sentencia SQL y ejecutarla: "+e);}

		cm.checkIn(db);
		// TODO Auto-generated method stub
		return test;
	}

}
